import xlrd3 as xlrd
import easygui
import sqlite3
from sqlite3 import Error
import loger
import config
import pathlib
import time
import reportTotext
log = loger.Logger('timetable',StreamLevel='info',FileLevel='info',fmt='%(asctime)s-%(levelname)s:\t%(message)s').logger

root=pathlib.Path(__file__).parent
db=sqlite3.connect(str(root/"classbook.db"))
cur = db.cursor()

def getFilePath():
    result = cur.execute("select * from sqlite_master where type = 'table' and name = 'setting'")
    row = result.fetchall()
    if(len(row)==0):
        cur.execute("CREATE TABLE 'setting' ('k' TEXT NOT NULL,  'v' TEXT,  PRIMARY KEY ('k'));")
        db.commit()
    r_=cur.execute("select v from setting where k = 'filepath'")
    r_=r_.fetchone()
    if(r_ == None):
        filepath=easygui.fileopenbox(title="file",default="*.xls",filetypes=["*.xls",'*.xlsx'])
        sql="INSERT INTO `setting` (`k`, `v`) VALUES ('filepath', '{}');".format(filepath)
        cur.execute(sql)
        db.commit()
    else:
        filepath=easygui.fileopenbox(title="file",default=pathlib.Path(r_[0])/"*.xls",filetypes=["*.xls",'*.xlsx'])
        if(filepath == None):
            return r_[0]
        else:
            cur.execute("UPDATE `setting` SET `v`= '{}' where k='filepath' ;".format(filepath))
            db.commit()
    return filepath


class fromxls :
    def __init__(self,filepath):
        log.debug("init {}".format(str(filepath)))
        self.wb=xlrd.open_workbook(filepath)
        self.names=self.wb.sheet_names()
        self.change_sheet_by_index(0)
        self.name=self.names[0]
    def change_sheet_by_name(self,name):
        if(name in self.names):
            self.name=name
            self.sheet=self.wb.sheet_by_name(name)
        else:
            rp.e(f"没有表\"{name}\"！")
    def change_sheet_by_index(self,index):
        self.name=self.names[index]
        self.sheet=self.wb.sheet_by_index(index)
    def getcell(self,row,col):
        return self.sheet.cell(row-1,col).value
    def getcellLable(self,row,col):
        return self.sheet.cell(row-1,self.getColIndex(col)).value
    def getrow(self,row):
        return self.sheet.row_slice(row-1)
    def getColIndex(self,lable):
        _n="ABCDEFGHIJKLNMOPQRSTUVWXYZ"
        return _n.index(lable)

def insertDbHalfClass(row1,row2,WeekColList,class_,times_):
    for w in WeekColList:
        col_=xls.getColIndex(WeekColList[w][0])
        if(row1[col_].value !=""):
            f_=row1[col_].value.find(config.namePrefix)
            if(f_ == -1):
                 if(row1[col_].value !=""):
                    sql="INSERT INTO main (\"name\", \"class\", \"week\", \"times\", \"course\",\"lesson_weights\") VALUES (\'{}\', \'{}\', \'{}\', \'{}\', \'{}\',1);".format(
                        row2[col_].value[1:-1],
                        class_,
                        WeekColList[w][1],
                        times_,
                        row1[col_].value
                        )
                    log.debug(sql)
                    cur.execute(sql)
            else:
                f2_=row2[col_].value.find(config.namePrefix)
                sql="INSERT INTO main (\"name\", \"class\", \"week\", \"times\", \"course\",\"lesson_weights\") VALUES (\'{0}\', \'{1}\', \'{2}\', \'{3}\', \'{4}\',0.5); ".format(
                    row1[col_].value[f_+1:-1],
                    class_,
                    WeekColList[w][1],
                    times_,
                    row1[col_].value[:f_]
                )
                log.debug(sql)
                cur.execute(sql)
                sql="INSERT INTO main (\"name\", \"class\", \"week\", \"times\", \"course\",\"lesson_weights\") VALUES (\'{}\', \'{}\', \'{}\', \'{}\', \'{}\',0.5);".format(
                    row2[col_].value[f2_+1:-1],
                    class_,
                    WeekColList[w][1],
                    times_,
                    row2[col_].value[:f2_],
                    )
                log.debug(sql)
                cur.execute(sql)
    db.commit()
def insertDbClass(row1,row2,WeekColList,class_,row_):
    for w in WeekColList:
        col_=xls.getColIndex(WeekColList[w][0])
        if(row1[col_].value !=""):
            
            sql="INSERT INTO main (\"name\", \"class\", \"week\", \"times\", \"course\",\"lesson_weights\") VALUES (\'{}\', \'{}\', \'{}\', \'{}\', \'{}\',1);".format(
                row2[col_].value[1:-1],
                class_,
                WeekColList[w][1],
                config.ClassRow.index(row_),
                row1[col_].value
                )
            log.debug(sql)
            cur.execute(sql)
    db.commit()
def inputdatabase():
    creatSql='''
    CREATE TABLE main (
    id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name  TEXT NOT NULL,
    class  TEXT NOT NULL,
    week  TEXT NOT NULL,
    times  TEXT NOT NULL,
    course  TEXT NOT NULL,
    real_course TEXT,
    lesson_weights INTEGER NOT NULL
    )
    ;
    '''
    try:
        sql="DROP TABLE IF EXISTS main;"
        cur.execute(sql)
        
    except:
        rp.e(("表清空失败"))
        db.rollback()
    else:
        rp.i("表已经清空")
        db.commit()

    try:
        cur.execute(creatSql) 
    except:
        rp.e("表创建失败")
        db.rollback()
    else:
        rp.i("表main创建成功")
        db.commit()

    try:
        cur.execute("DROP TABLE IF EXISTS lable;") 
        cur.execute("""
            CREATE TABLE "lable" (
            "sn"  INTEGER NOT NULL,
            "type"  TEXT NOT NULL,
            "valeu"  TEXT
            );
            """)
    except Error as  e:
        rp.e(f"{str(e)}")
    else:
        rp.i("表lable创建成功")
        db.commit()
    log.info("开始录入课时名称")
    xls.change_sheet_by_index(0)
    da_=[]
    for i,v in enumerate(config.ClassRow) :
        rowval=xls.getrow(v)
        title=rowval[config.classTimeLableCol]
        da_.append((i,title.value))
    try:
        cur.execute("DELETE FROM `lable`;")
        cur.executemany("INSERT INTO `lable` (`sn`, `type`, `valeu`) VALUES (?, 'session',?);",da_)
    except Error as e:
        rp.e(f"数据录入有误 {str(e)}")
        db.rollback()
    else:
        db.commit()
    da_=[]
    for i,k in enumerate(config.WeekColList):
        # print(i,k,config.WeekColList[k][1])
        da_.append((config.WeekColList[k][1],k))

    try:
        cur.executemany("INSERT INTO `lable` (`sn`, `type`, `valeu`) VALUES (?, 'week',?);",da_)
    except Error as e:
        rp.e(f"数据录入有误 {str(e)}")
        db.rollback()
    else:
        db.commit()
    da_=None

    log.info("开始录入课表")
    for i in xls.names: #班级课表依次录入
        
        if(i[-1:]!= "班"):
            log.debug(f"忽略<{i}>表")
            continue
        else:
            log.info("开始录入{}课表".format(i))
            xls.change_sheet_by_name(i)
        for n in config.ClassRow: 
            if n in config.halfClass:
                _class1=xls.getrow(n)
                _class2=xls.getrow(n+1)
                insertDbHalfClass(_class1,_class2,config.WeekColList,i,config.ClassRow.index(n))
            else:
                _class1=xls.getrow(n)
                _class2=xls.getrow(n+1)
                insertDbClass(_class1,_class2,config.WeekColList,i,n)
        for n in config.FragmentedInputList: #零散课时录入
            if(i in n[3]):
                row1=xls.getrow(n[1])
                row2=xls.getrow(n[1]+1)
                insertDbHalfClass(row1,row2,n[0],i,n[2])
    for i in config.FragmentedDeleteList:
        for cl in i[0]:
            sql="DELETE FROM main WHERE (week={} and times={} and class='{}' );".format(i[1],i[2],cl)
            cur.execute(sql)
        db.commit()
    log.info("课表录入完成")
    r=cur.execute("select class, sum(lesson_weights) from main group by class;")
    r=r.fetchall()
    for i in r:
        if(i[1]!=config.totalClassTime):
            rp.e("{}课时录入{}节".format(i[0],i[1]))
        else:
            rp.i("{}课时录入{}节".format(i[0],i[1]))
    allclassname=[]
    for i in config.grade:
        allclassname=allclassname+config.grade[i]
    log.debug(f"all class name :{allclassname}")
    for i in allclassname:
        r=cur.execute(f"select course from main where class='{i}';")
        r=r.fetchall()
        if(len(r)==0):
            rp.e(f"{i}未查到课时，请检查班级名！")
def MappingReversion():
    log.info("\n\n开始还原映射")
    import courseMapping as map
    for i in map.courseMapping:
        for c in i['class']:
            for m in i["course"]:
                sql="select * from main where class='{}' and course='{}'".format(c,m)
                r=cur.execute(sql).fetchall()
                if(len(r)<=0):
                    rp.e(f"未找到\t{c}\t\t{m}课")
                log.debug(len(r))
                sql="UPDATE `main` SET `real_course`='{0}' WHERE (`class`='{1}' and `course`='{2}');".format(i["course"][m],c,m)
                cur.execute(sql)
                log.debug(sql)
            db.commit()
    cur.execute("update main set real_course = course where real_course is null;")
    db.commit()
    r=cur.execute("select real_course from main group by real_course;")
    r=r.fetchall()
    str_="所有科目：\n"
    n_=1
    for i in r:
        str_=str_+f"{n_}\t {i[0]}\n"
        n_=n_+1
    rp.i(str_)
    r=None
    str=None
    log.info("完成映射还原")

def exit_(t=3):
    for i in range(t+1):
        print('\r',f"已完成，{t-i}秒后退出",end='')
        time.sleep(1)

if __name__ == "__main__":
    filepath=pathlib.Path(getFilePath())
    rp=reportTotext.reportTotext(filepath,log)
    xls=fromxls(filepath)
    inputdatabase()
    MappingReversion()
    import Friday_afternoon_9grade_conflict_check
    Friday_afternoon_9grade_conflict_check.c(db,rp,log)
    import BaseCheck
    BaseCheck.basecheck(db,rp,log)
    print(rp.error)
    rp.save()
    exit_(15)